﻿using Common.NpoiHelper.Attributes;
using Common.NpoiHelper.Model;
using NPOI.HSSF.UserModel;
using NPOI.SS.Formula.Eval;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace Common.NpoiHelper
{
    public class NpoiHelper
    {
        /// <summary>
        /// 把excel转化为Datatable
        /// </summary>
        /// <param name="FilePath">文件路径</param>
        /// <returns></returns>
        public static DataTable ExcelToDataTable(string FilePath)
        {
            var workbook = WorkbookFactory.Create(FilePath);
            var sheet = workbook.GetSheetAt(0);
            return SheetToDataTable(sheet);
        }
        /// <summary>
        /// 把文件流转为Datatable
        /// </summary>
        /// <param name="fs">文件流</param>
        /// <returns></returns>
        public static DataTable ExcelToDataTable(FileStream fs)
        {
            var workbook = WorkbookFactory.Create(fs);
            var sheet = workbook.GetSheetAt(0);
            return SheetToDataTable(sheet);
        }
        /// <summary>
        /// 把excel文件转为datatable
        /// </summary>
        /// <param name="FilePath">文件路径</param>
        /// <param name="SheetIndex">sheet页索引</param>
        /// <returns></returns>
        public static DataTable ExcelToDataTable(string FilePath, int SheetIndex)
        {
            var workbook = WorkbookFactory.Create(FilePath);
            var sheet = workbook.GetSheetAt(SheetIndex);
            return SheetToDataTable(sheet);
        }
        /// <summary>
        /// 把文件流转为datatable
        /// </summary>
        /// <param name="fs">文件流</param>
        /// <param name="SheetIndex">sheet索引</param>
        /// <returns></returns>
        public static DataTable ExcelToDataTable(FileStream fs, int SheetIndex)
        {
            var workbook = WorkbookFactory.Create(fs);
            var sheet = workbook.GetSheetAt(SheetIndex);
            return SheetToDataTable(sheet);
        }

        /// <summary>
        /// 把excel转换成为一个实体list（默认读取第一个sheet）
        /// </summary>
        /// <typeparam name="T">实体类</typeparam>
        /// <param name="FilePath">文件路径</param>
        /// <returns></returns>
        public static List<T> ExcelToObject<T>(string FilePath) where T : class, new()
        {
            var workbook = WorkbookFactory.Create(FilePath);
            var sheet = workbook.GetSheetAt(0);
            return SheetToObject<T>(sheet);
        }
        /// <summary>
        /// 把excel转换成为一个实体list
        /// </summary>
        /// <typeparam name="T">实体类</typeparam>
        /// <param name="FilePath">文件路径</param>
        /// <param name="SheetIndex">sheet页的索引</param>
        /// <returns></returns>
        public static List<T> ExcelToObject<T>(string FilePath, int SheetIndex) where T : class, new()
        {
            var workbook = WorkbookFactory.Create(FilePath);
            var sheet = workbook.GetSheetAt(SheetIndex);
            return SheetToObject<T>(sheet);
        }
        /// <summary>
        /// 把excel转换成为一个实体list（默认读取第一个sheet
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="fs">文件流</param>
        /// <returns></returns>
        public static List<T> ExcelToObject<T>(FileStream fs) where T : class, new()
        {
            var workbook = WorkbookFactory.Create(fs);
            var sheet = workbook.GetSheetAt(0);
            return SheetToObject<T>(sheet);
        }
        /// <summary>
        /// 把excel转换成为一个实体list
        /// </summary>
        /// <typeparam name="T">实体类</typeparam>
        /// <param name="FilePath">文件路径</param>
        /// <param name="SheetName">sheet名称</param>
        /// <returns></returns>
        public static List<T> ExcelToObject<T>(string FilePath, string SheetName) where T : class, new()
        {
            var workbook = WorkbookFactory.Create(FilePath);
            var sheet = workbook.GetSheet(SheetName);
            return SheetToObject<T>(sheet);
        }
        /// <summary>
        /// 把excel转换成为一个实体list
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="fs">文件流</param>
        /// <param name="SheetName">sheet名称</param>
        /// <returns></returns>
        public static List<T> ExcelToObject<T>(FileStream fs, string SheetName) where T : class, new()
        {
            var workbook = WorkbookFactory.Create(fs);
            var sheet = workbook.GetSheet(SheetName);
            return SheetToObject<T>(sheet);
        }

        /// <summary>
        /// 以文件名的形式导出excel文件
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list">实例集合</param>
        /// <param name="SheetName">sheet名</param>
        /// <param name="DoSomething">后续对已形成的sheet进行操作</param>
        /// <returns>生成的文件的名称</returns>
        public static string ExportExcelToFilePath<T>(IEnumerable<T> list, string SheetName = null, Action<ISheet> DoSomething = null) where T : class, new()
        {

            XSSFWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = null;
            if (!string.IsNullOrEmpty(SheetName))
            {
                sheet = workbook.CreateSheet(SheetName);
            }
            else
            {
                sheet = workbook.CreateSheet("sheet");
            }
            sheet = DataToSheet(sheet, list);
            if (DoSomething != null)
            {
                DoSomething.Invoke(sheet);
            }
            //获取当前路径
            string BasePath = $"{Directory.GetCurrentDirectory()}/ExportExcel/";
            return Save(workbook, BasePath);
        }
        /// <summary>
        /// 导出数据结构一致但是需要根据条件分别放在不同sheet页的表格
        /// </summary>
        /// <typeparam name="T">泛型T</typeparam>
        /// <param name="DoSomething">后续对sheet进行二次操作</param>
        /// <param name="DataAndNames">数据和sheet名</param>
        /// <returns></returns>
        public static string ExportExcelToFilePath<T>(Action<List<ISheet>> DoSomething = null,params(IEnumerable<T> list, string SheetName)[] DataAndNames) where T : class, new()
        {
            XSSFWorkbook workbook = new XSSFWorkbook();
            List<ISheet> sheets=new List<ISheet>();
            foreach (var item in DataAndNames)
            {
                ISheet sheet = workbook.CreateSheet(item.SheetName);
                sheet = DataToSheet(sheet, item.list);
                sheets.Add(sheet);
            }
            if (DoSomething != null)
            {
                DoSomething.Invoke(sheets);
            }
            string BasePath = $"{Directory.GetCurrentDirectory()}/ExportExcel/";
            return Save(workbook, BasePath);
        }
        /// <summary>
        /// 以文件名的形式导出excel
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <typeparam name="T1">泛型1</typeparam>
        /// <param name="DataAndName">数据list和sheet名称</param>
        /// <param name="DataAndName1">数据list和sheet名称</param>
        /// <param name="DoSomething">对形成的sheet再次进行操作,可以设置格式等</param>
        /// <returns></returns>
        public static string ExportExcelToFilePath<T, T1>((IEnumerable<T> list, string SheetName) DataAndName, (IEnumerable<T1> list, string SheetName) DataAndName1,Action<ISheet, ISheet> DoSomething=null)
            where T : class, new()
            where T1 : class, new()
        {

            XSSFWorkbook workbook = new XSSFWorkbook();
            var sheets = GetSheet(workbook, DataAndName, DataAndName1);
            if (DoSomething != null)
            {
                DoSomething.Invoke(sheets.sheet, sheets.sheet1);
            }
            //获取当前路径
            string BasePath = $"{Directory.GetCurrentDirectory()}/ExportExcel/";
            return Save(workbook, BasePath);
        }

        public static string ExportExcelToFilePath<T, T1, T2>((IEnumerable<T> list, string SheetName) DataAndName, (IEnumerable<T1> list, string SheetName) DataAndName1, (IEnumerable<T2> list, string SheetName) DataAndName2, Action<ISheet, ISheet, ISheet> DoSomething = null)
            where T : class, new()
            where T1 : class, new()
            where T2 : class, new()
        {

            XSSFWorkbook workbook = new XSSFWorkbook();
            var sheets = GetSheet(workbook, DataAndName, DataAndName1, DataAndName2);
            if (DoSomething != null)
            {
                DoSomething.Invoke(sheets.sheet, sheets.sheet1,sheets.sheet2);
            }
            //获取当前路径
            string BasePath = $"{Directory.GetCurrentDirectory()}/ExportExcel/";
            return Save(workbook, BasePath);
        }

        public static string ExportExcelToFilePath<T, T1, T2, T3>((IEnumerable<T> list, string SheetName) DataAndName, (IEnumerable<T1> list, string SheetName) DataAndName1, (IEnumerable<T2> list, string SheetName) DataAndName2, (IEnumerable<T3> list, string SheetName) DataAndName3, Action<ISheet, ISheet, ISheet,ISheet> DoSomething = null)
            where T : class, new()
            where T1 : class, new()
            where T2 : class, new()
            where T3 : class, new()
        {

            XSSFWorkbook workbook = new XSSFWorkbook();
            var sheets = GetSheet(workbook, DataAndName, DataAndName1, DataAndName2, DataAndName3);
            if (DoSomething != null)
            {
                DoSomething.Invoke(sheets.sheet, sheets.sheet1, sheets.sheet2,sheets.sheet3);
            }
            //获取当前路径
            string BasePath = $"{Directory.GetCurrentDirectory()}/ExportExcel/";
            return Save(workbook, BasePath);
        }

        public static string ExportExcelToFilePath<T, T1, T2, T3, T4>((IEnumerable<T> list, string SheetName) DataAndName, (IEnumerable<T1> list, string SheetName) DataAndName1, (IEnumerable<T2> list, string SheetName) DataAndName2, (IEnumerable<T3> list, string SheetName) DataAndName3, (IEnumerable<T4> list, string SheetName) DataAndName4, Action<ISheet, ISheet, ISheet, ISheet,ISheet> DoSomething = null)
            where T : class, new()
            where T1 : class, new()
            where T2 : class, new()
            where T3 : class, new()
            where T4 : class, new()
        {

            XSSFWorkbook workbook = new XSSFWorkbook();
            var sheets = GetSheet(workbook, DataAndName, DataAndName1, DataAndName2, DataAndName3, DataAndName4);
            if (DoSomething != null)
            {
                DoSomething.Invoke(sheets.sheet, sheets.sheet1, sheets.sheet2, sheets.sheet3,sheets.sheet4);
            }
            //获取当前路径
            string BasePath = $"{Directory.GetCurrentDirectory()}/ExportExcel/";
            return Save(workbook, BasePath);
        }
        /// <summary>
        /// 保存excel文件
        /// </summary>
        /// <param name="workbook">workbook实例</param>
        /// <param name="BasePath">保存位置</param>
        /// <returns>文件名</returns>
        private static string Save(XSSFWorkbook workbook, string BasePath)
        {
            if (!Directory.Exists(BasePath))
            {
                Directory.CreateDirectory(BasePath);
            }
            string Name = $"{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx";
            while (File.Exists(BasePath + Name))
            {
                Name = $"{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx";
            }
            using (FileStream fs = File.Open(BasePath + Name, FileMode.Create))
            {
                workbook.Write(fs);
            }
            return Name;
        }

        /// <summary>
        /// 根据实体导出excel流
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list">实体list</param>
        /// <param name="SheetName">sheet页名称</param>
        /// <param name="DoSomething">整个sheet生成操作完成后,可以后续对sheet进行二次操作</param>
        /// <returns>excel流</returns>
        public static byte[] ExportExcelToStream<T>(IEnumerable<T> list, string SheetName = null, Action<ISheet> DoSomething = null) where T : class, new()
        {
            XSSFWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = null;
            if (!string.IsNullOrEmpty(SheetName))
            {
                sheet = workbook.CreateSheet(SheetName);
            }
            else
            {
                sheet = workbook.CreateSheet("sheet");
            }
            sheet = DataToSheet(sheet, list);
            if (DoSomething != null)
            {
                DoSomething.Invoke(sheet);
            }
            using (MemoryStream s = new MemoryStream())
            {
                workbook.Write(s);
                return s.ToArray();
            }
        }
        /// <summary>
        /// 导出数据结构一致但是需要根据条件分别放在不同sheet页的表格
        /// </summary>
        /// <typeparam name="T">泛型T</typeparam>
        /// <param name="DoSomething">后续对表格进行的操作</param>
        /// <param name="DataAndNames">数据源和sheet名称</param>
        /// <returns>二进制流</returns>
        public static byte[] ExportExcelToStream<T>(Action<List<ISheet>> DoSomething = null, params (IEnumerable<T> list, string SheetName)[] DataAndNames) where T : class, new()
        {
            XSSFWorkbook workbook = new XSSFWorkbook();
            List<ISheet> sheets = new List<ISheet>();
            foreach (var item in DataAndNames)
            {
                ISheet sheet = workbook.CreateSheet(item.SheetName);
                sheet = DataToSheet(sheet, item.list);
                sheets.Add(sheet);
            }
            if (DoSomething != null)
            {
                DoSomething.Invoke(sheets);
            }
            using (MemoryStream s = new MemoryStream())
            {
                workbook.Write(s);
                return s.ToArray();
            }
        }
        public static byte[] ExportExcelToStream<T, T1>((IEnumerable<T> list, string SheetName) DataAndName, (IEnumerable<T1> list, string SheetName) DataAndName1, Action<ISheet, ISheet> DoSomething = null)
            where T : class, new()
            where T1 : class, new()
        {
            XSSFWorkbook workbook = new XSSFWorkbook();
            var sheets = GetSheet(workbook, DataAndName, DataAndName1);
            if (DoSomething != null)
            {
                DoSomething.Invoke(sheets.sheet,sheets.sheet1);
            }
            using (MemoryStream s = new MemoryStream())
            {
                workbook.Write(s);
                return s.ToArray();
            }
        }

        public static byte[] ExportExcelToStream<T, T1, T2>((IEnumerable<T> list, string SheetName) DataAndName, (IEnumerable<T1> list, string SheetName) DataAndName1, (IEnumerable<T2> list, string SheetName) DataAndName2, Action<ISheet, ISheet, ISheet> DoSomething = null)
            where T : class, new()
            where T1 : class, new()
            where T2 : class, new()
        {
            XSSFWorkbook workbook = new XSSFWorkbook();
            var sheets = GetSheet(workbook, DataAndName, DataAndName1, DataAndName2);
            if (DoSomething != null)
            {
                DoSomething.Invoke(sheets.sheet, sheets.sheet1,sheets.sheet2);
            }
            using (MemoryStream s = new MemoryStream())
            {
                workbook.Write(s);
                return s.ToArray();
            }
        }

        public static byte[] ExportExcelToStream<T, T1, T2, T3>((IEnumerable<T> list, string SheetName) DataAndName, (IEnumerable<T1> list, string SheetName) DataAndName1, (IEnumerable<T2> list, string SheetName) DataAndName2, (IEnumerable<T3> list, string SheetName) DataAndName3, Action<ISheet, ISheet, ISheet , ISheet> DoSomething = null)
            where T : class, new()
            where T1 : class, new()
            where T2 : class, new()
            where T3 : class, new()
        {
            XSSFWorkbook workbook = new XSSFWorkbook();
            var sheets = GetSheet(workbook, DataAndName, DataAndName1, DataAndName2, DataAndName3);
            if (DoSomething != null)
            {
                DoSomething.Invoke(sheets.sheet, sheets.sheet1, sheets.sheet2,sheets.sheet3);
            }
            using (MemoryStream s = new MemoryStream())
            {
                workbook.Write(s);
                return s.ToArray();
            }
        }

        public static byte[] ExportExcelToStream<T, T1, T2, T3, T4>((IEnumerable<T> list, string SheetName) DataAndName, (IEnumerable<T1> list, string SheetName) DataAndName1, (IEnumerable<T2> list, string SheetName) DataAndName2, (IEnumerable<T3> list, string SheetName) DataAndName3, (IEnumerable<T4> list, string SheetName) DataAndName4, Action<ISheet, ISheet, ISheet, ISheet, ISheet> DoSomething = null)
            where T : class, new()
            where T1 : class, new()
            where T2 : class, new()
            where T3 : class, new()
            where T4 : class, new()
        {
            XSSFWorkbook workbook = new XSSFWorkbook();
            var sheets = GetSheet(workbook, DataAndName, DataAndName1, DataAndName2, DataAndName3, DataAndName4);
            if (DoSomething != null)
            {
                DoSomething.Invoke(sheets.sheet, sheets.sheet1, sheets.sheet2, sheets.sheet3,sheets.sheet4);
            }
            using (MemoryStream s = new MemoryStream())
            {
                workbook.Write(s);
                return s.ToArray();
            }
        }
        /// <summary>
        /// 对excel进行批注
        /// </summary>
        /// <param name="filePath">文件地址</param>
        /// <param name="comments">评价模型list</param>
        /// <param name="startRow">从哪一行开始</param>
        /// <param name="cellColour">是否给错误行和错误cell涂色</param>
        public static void CommentExcel(string filePath,IEnumerable<CommentModel> comments,int startRow = 0,bool cellColour=true)
        {
            var workbook = WorkbookFactory.Create(filePath);
            var group = comments.GroupBy(t => t.SheetIndex);
            List<int> keys= group.Select(t=>t.Key).ToList();
            //错误数据单元格样式
            ICellStyle errorCellStyle = workbook.CreateCellStyle();
            errorCellStyle.FillPattern = FillPattern.SolidForeground;
            errorCellStyle.FillForegroundColor = IndexedColors.Red.Index;
            //错误数据整行样式
            ICellStyle errorRowStyle = workbook.CreateCellStyle();
            errorRowStyle.FillPattern= FillPattern.SolidForeground;
            errorRowStyle.FillForegroundColor = IndexedColors.Yellow.Index;
            Dictionary<int, List<int>> records = new Dictionary<int, List<int>>();
            keys.ForEach(t =>
            {
                ISheet sheet = workbook.GetSheetAt(t);
                IDrawing p = sheet.CreateDrawingPatriarch();
                
                foreach (var comment in group.Where(a => a.Key == t).FirstOrDefault())
                {
                    IClientAnchor clientAnchor = p.CreateAnchor(0, 0, 0, 3, 3, 3, 5, 6);
                    IRow row = sheet.GetRow(comment.RowIndex + startRow);
                    for (int i = 0; i < row.Cells.Count; i++)
                    {
                        ICell cell = row.GetCell(i);
                        if (cell == null)
                        {
                            continue;
                        }
                        if (i == comment.ColumnIndex)
                        {
                            IComment commentStyle = p.CreateCellComment(clientAnchor);
                            commentStyle.Visible = false;
                            commentStyle.Author = "yz";
                            commentStyle.String = workbook.GetCreationHelper().CreateRichTextString(comment.Message);
                            if (cell.CellComment!=null)
                            {
                                cell.RemoveCellComment();
                            }
                            cell.CellComment = commentStyle;
                            //cell.CellStyle = errorCellStyle;

                        }
                        else
                        {
                            cell.CellStyle = errorRowStyle;
                        }
                    }
                }
            });
            using(FileStream fs = File.Open(filePath,FileMode.Open))
            {
                workbook.Write(fs);
            }
           
        }
        /// <summary>
        /// 构造多个sheet,返回workbook
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <typeparam name="T1"></typeparam>
        /// <param name="DataAndName"></param>
        /// <param name="DataAndName1"></param>
        /// <returns></returns>
        private static (ISheet sheet, ISheet sheet1) GetSheet<T, T1>(XSSFWorkbook workbook, (IEnumerable<T> list, string SheetName) DataAndName, (IEnumerable<T1> list, string SheetName) DataAndName1)
            where T : class, new()
            where T1 : class, new()
        {
            ISheet sheet = workbook.CreateSheet(DataAndName.SheetName);
            sheet = DataToSheet(sheet, DataAndName.list);
            ISheet sheet1 = workbook.CreateSheet(DataAndName1.SheetName);
            sheet1 = DataToSheet(sheet1, DataAndName1.list);
            return (sheet, sheet1);
        }

        private static (ISheet sheet, ISheet sheet1, ISheet sheet2) GetSheet<T, T1, T2>(XSSFWorkbook workbook, (IEnumerable<T> list, string SheetName) DataAndName, (IEnumerable<T1> list, string SheetName) DataAndName1, (IEnumerable<T2> list, string SheetName) DataAndName2)
            where T : class, new()
            where T1 : class, new()
            where T2 : class, new()
        {
            ISheet sheet = workbook.CreateSheet(DataAndName.SheetName);
            sheet = DataToSheet(sheet, DataAndName.list);
            ISheet sheet1 = workbook.CreateSheet(DataAndName1.SheetName);
            sheet1 = DataToSheet(sheet1, DataAndName1.list);
            ISheet sheet2 = workbook.CreateSheet(DataAndName2.SheetName);
            sheet2 = DataToSheet(sheet2, DataAndName2.list);
            return (sheet, sheet1, sheet2);
        }

        private static (ISheet sheet, ISheet sheet1, ISheet sheet2, ISheet sheet3) GetSheet<T, T1, T2, T3>(XSSFWorkbook workbook, (IEnumerable<T> list, string SheetName) DataAndName, (IEnumerable<T1> list, string SheetName) DataAndName1, (IEnumerable<T2> list, string SheetName) DataAndName2, (IEnumerable<T3> list, string SheetName) DataAndName3)
            where T : class, new()
            where T1 : class, new()
            where T2 : class, new()
            where T3 : class, new()
        {
            ISheet sheet = workbook.CreateSheet(DataAndName.SheetName);
            sheet = DataToSheet(sheet, DataAndName.list);
            ISheet sheet1 = workbook.CreateSheet(DataAndName1.SheetName);
            sheet1 = DataToSheet(sheet1, DataAndName1.list);
            ISheet sheet2 = workbook.CreateSheet(DataAndName2.SheetName);
            sheet2 = DataToSheet(sheet2, DataAndName2.list);
            ISheet sheet3 = workbook.CreateSheet(DataAndName3.SheetName);
            sheet3 = DataToSheet(sheet3, DataAndName3.list);
            return (sheet, sheet1, sheet2, sheet3);
        }

        private static (ISheet sheet, ISheet sheet1, ISheet sheet2, ISheet sheet3, ISheet sheet4) GetSheet<T, T1, T2, T3, T4>(XSSFWorkbook workbook, (IEnumerable<T> list, string SheetName) DataAndName, (IEnumerable<T1> list, string SheetName) DataAndName1, (IEnumerable<T2> list, string SheetName) DataAndName2, (IEnumerable<T3> list, string SheetName) DataAndName3, (IEnumerable<T4> list, string SheetName) DataAndName4)
            where T : class, new()
            where T1 : class, new()
            where T2 : class, new()
            where T3 : class, new()
            where T4 : class, new()
        {
            ISheet sheet = workbook.CreateSheet(DataAndName.SheetName);
            sheet = DataToSheet(sheet, DataAndName.list);
            ISheet sheet1 = workbook.CreateSheet(DataAndName1.SheetName);
            sheet1 = DataToSheet(sheet1, DataAndName1.list);
            ISheet sheet2 = workbook.CreateSheet(DataAndName2.SheetName);
            sheet2 = DataToSheet(sheet2, DataAndName2.list);
            ISheet sheet3 = workbook.CreateSheet(DataAndName3.SheetName);
            sheet3 = DataToSheet(sheet3, DataAndName3.list);
            ISheet sheet4 = workbook.CreateSheet(DataAndName4.SheetName);
            sheet4 = DataToSheet(sheet4, DataAndName3.list);
            return (sheet, sheet1, sheet2, sheet3, sheet4);
        }

        /// <summary>
        /// datatable转为sheet
        /// </summary>
        /// <typeparam name="T">泛型T</typeparam>
        /// <param name="Sheet">sheet实例</param>
        /// <param name="list">数据集合</param>
        /// <returns></returns>
        private static ISheet DataToSheet<T>(ISheet Sheet, IEnumerable<T> list) where T : class, new()
        {
            var dd = GetPropNames<T>(OperationType.Export);
            int ColumnIndex = 0;
            int RowIndex = 0;

            //表头赋值
            IRow Header = Sheet.CreateRow(ColumnIndex);
            foreach (var ColumnName in dd.Keys)
            {
                ICell cell = Header.CreateCell(ColumnIndex);
                cell.SetCellValue(ColumnName);

                if (dd[ColumnName].WidthSize > 0)
                {
                    Sheet.SetColumnWidth(ColumnIndex, dd[ColumnName].WidthSize);
                }
                ColumnIndex++;
            }
            ColumnIndex = 0;
            RowIndex++;
            //内容赋值
            Type type = typeof(T);
            foreach (T Data in list)
            {
                if (Data == null) continue;
                IRow row = Sheet.CreateRow(RowIndex);
                foreach (var DataName in dd.Keys)
                {
                    ICell cell = row.CreateCell(ColumnIndex);
                    ColumnIndex++;
                    PropertyInfo prop = dd[DataName].Prop;
                    if (prop == null)
                    {
                        continue;
                    } 
                    object value = prop.GetValue(Data);
                    if (value == null) 
                    {
                        continue;
                    } 
                    cell.SetCellValue(value.ToString());

                }
                RowIndex++;
                ColumnIndex = 0;
            }
            return Sheet;

        }

        /// <summary>
        /// 把excel转成datatable
        /// </summary>
        /// <param name="FilePath"></param>
        /// <returns></returns>
        private static DataTable SheetToDataTable(ISheet sheet)
        {
            IRow row;
            ICell cell;
            //先把header取出
            row = sheet.GetRow(0);
            int RowNum = row.RowNum;
            int ColumnNum = sheet.PhysicalNumberOfRows;
            DataTable dataTable = new DataTable();
            //构造dt表头
            for (int i = 0; i < RowNum - 1; i++)
            {
                cell = row.GetCell(i);
                DataColumn column = new DataColumn(cell.StringCellValue);
                dataTable.Columns.Add(column);
            }
            for (int i = 1; i < ColumnNum - 1; i++)
            {
                row = sheet.GetRow(i);
                DataRow data = dataTable.NewRow();
                for (int j = 0; j < RowNum - 1; j++)
                {
                    cell = row.GetCell(j);
                    data[j] = cell;
                }
                dataTable.Rows.Add(data);
            }
            return dataTable;
        }

        /// <summary>
        /// 把sheet转成list
        /// </summary>
        /// <typeparam name="T">实体</typeparam>
        /// <param name="sheet">sheet页</param>
        /// <returns></returns>
        private static List<T> SheetToObject<T>(ISheet sheet) where T : class, new()
        {
            List<T> list = new List<T>();
            IRow row;
            ICell cell;
            //表头列
            var HeaderRow = sheet.GetRow(0);
            int RowNum = HeaderRow.LastCellNum;
            int ColumnNum = sheet.PhysicalNumberOfRows;
            //映射关系
            var dd = GetPropNames<T>(OperationType.Import);
            //数据列
            for (int i = 1; i <= ColumnNum - 1; i++)
            {
                row = sheet.GetRow(i);
                T entity = new T();
                for (int j = 0; j <= RowNum - 1; j++)
                {
                    var header = HeaderRow.GetCell(j);
                    if (header == null)
                    {
                        continue;
                    }
                    var HeaderCellValue = header.ToString();
                    cell = row.GetCell(j);
                    if (!dd.TryGetValue(HeaderCellValue.ToString(), out (PropertyInfo EntityProp, int WidthSize) Result))
                    {
                        continue;
                    }
                    var value = Convert.ChangeType(GetCellValue(cell), Result.EntityProp.PropertyType);
                    ExpressionHelper.ExpressionHelper.GetSetter<T>(Result.EntityProp)(entity, value);
                }
                list.Add(entity);
            }
            return list;
        }
        /// <summary>
        /// 获取实体和excel表头的映射关系
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        private static Dictionary<string, (PropertyInfo Prop, int WidthSize)> GetPropNames<T>(OperationType type) where T : class, new()
        {
            Dictionary<string, (PropertyInfo, int)> dd = new Dictionary<string, (PropertyInfo, int)>();
            Type t = typeof(T);
            List<PropertyInfo> props = t.GetProperties().OrderBy(t =>
             {
                 var attr = t.GetCustomAttribute<NpoiAttribute>();
                 if (attr != null)
                 {
                     return attr.Position;
                 }
                 return 999;
             }).ToList();
            foreach (var prop in props)
            {
                var attr = prop.GetCustomAttribute<NpoiAttribute>();
                if (attr != null)
                {
                    if(attr.OperationType== OperationType.Import_Export|| attr.OperationType == type)
                    {
                        dd.Add(attr.HeaderName, (prop, attr.ColumnWidth));
                    }
                }
            }
            return dd;
        }
        /// <summary>
        /// 获取cell的值,并转换为相应的类型
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        private static object GetCellValue(ICell cell)
        {
            if (cell == null) return "";
            object value = null;
            switch (cell.CellType)
            {
                case CellType.Numeric:    //用于取出数值和公式类型的数据 
                    value = DateUtil.IsCellDateFormatted(cell) ? cell.DateCellValue.ToString("yyyy-MM-dd") : cell.NumericCellValue.ToString();
                    break;
                case CellType.Error:
                    value = ErrorEval.GetText(cell.ErrorCellValue);
                    break;
                case CellType.Formula:
                    switch (cell.CachedFormulaResultType)
                    {
                        case CellType.String:
                            string strFORMULA = cell.StringCellValue;
                            if (strFORMULA != null && strFORMULA.Length > 0)
                            {
                                value = strFORMULA.ToString();
                            }
                            break;
                        case CellType.Numeric:
                            value = Convert.ToString(cell.NumericCellValue);
                            break;
                        case CellType.Boolean:
                            value = Convert.ToString(cell.BooleanCellValue);
                            break;
                        case CellType.Error:
                            value = ErrorEval.GetText(cell.ErrorCellValue);
                            break;
                        default:
                            value = "";
                            break;
                    }
                    break;
                case CellType.Boolean:
                    // Boolean type
                    value = cell.BooleanCellValue.ToString();
                    break;

                case CellType.Blank:
                    break;

                default:
                    // String type
                    value = cell.StringCellValue.Trim();
                    break;
            }
            return value;
        }
    }
}
